﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class VoteItemDao
    {
        public bool Delete(SqlConnection conn, int itemId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Vote_Item] ");
            builder.Append("WHERE ");
            builder.Append("\t[ItemId] = @ItemId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ItemId", SqlDbType.Int) };
            commandParameters[0].Value = itemId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, VoteItemData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Vote_Item] ");
            builder.Append("( ");
            builder.Append("\t[VoteId], ");
            builder.Append("\t[ItemName], ");
            builder.Append("\t[VoteCount], ");
            builder.Append("\t[VoteUser] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@VoteId, ");
            builder.Append("\t@ItemName, ");
            builder.Append("\t@VoteCount, ");
            builder.Append("\t@VoteUser ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@VoteId", SqlDbType.Int);
            commandParameters[0].Value = data.VoteId;
            commandParameters[1] = new SqlParameter("@ItemName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.ItemName;
            commandParameters[2] = new SqlParameter("@VoteCount", SqlDbType.Int);
            commandParameters[2].Value = data.VoteCount;
            commandParameters[3] = new SqlParameter("@VoteUser", SqlDbType.NText);
            if (data.VoteUserIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.VoteUser;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByItemId(SqlConnection conn, int itemId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[ItemId], ");
            builder.Append("\t[VoteId], ");
            builder.Append("\t[ItemName], ");
            builder.Append("\t[VoteCount], ");
            builder.Append("\t[VoteUser]\t ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vote_Item] ");
            builder.Append("WHERE ");
            builder.Append("\t[ItemId] = @ItemId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@ItemId", SqlDbType.Int) };
            commandParameters[0].Value = itemId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote_Item";
            return set.Tables[0];
        }

        public DataTable SelectByVoteId(SqlConnection conn, int voteId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DECLARE @TotalCount\tint ");
            builder.Append("SELECT ");
            builder.Append("\t@TotalCount = SUM([VoteCount]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vote_Item] ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            builder.Append("SELECT ");
            builder.Append("\t[ItemId], ");
            builder.Append("\t[VoteId], ");
            builder.Append("\t[ItemName], ");
            builder.Append("\t[VoteCount], ");
            builder.Append("\t[VoteUser], ");
            builder.Append("\tdbo.GetUserNames(CAST([VoteUser] AS nvarchar(4000))) AS VoteUserName, ");
            builder.Append("\t@TotalCount AS TotalCount ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vote_Item] ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[ItemId] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@VoteId", SqlDbType.Int) };
            commandParameters[0].Value = voteId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote_Item";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, VoteItemData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Vote_Item] ");
            builder.Append("SET\t ");
            builder.Append("\t[ItemName] = @ItemName\t ");
            builder.Append("WHERE ");
            builder.Append("\t[ItemId] = @ItemId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@ItemId", SqlDbType.Int);
            commandParameters[0].Value = data.ItemId;
            commandParameters[1] = new SqlParameter("@ItemName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.ItemName;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Vote(SqlConnection conn, int voteId, int itemId, bool isAnonymity, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Vote] ");
            builder.Append("SET ");
            builder.Append("\t[Readers] = ISNULL(CAST([Readers] AS nvarchar(4000)),'') + @UserId + ',' ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            builder.Append("\tAND NOT CHARINDEX(',' + @UserId + ',',',' + ISNULL(CAST([Readers] AS nvarchar(4000)),'') + ',') > 0 ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Vote_Item] ");
            builder.Append("SET\t ");
            builder.Append("\t[VoteCount] = [VoteCount] + 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[ItemId] = @ItemId ");
            builder.Append("IF @IsAnonymity = 0 ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Vote_Item] ");
            builder.Append("\tSET\t ");
            builder.Append("\t\t[VoteUser] = ISNULL(CAST([VoteUser] AS nvarchar(4000)),'') + @UserId + ',' ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[ItemId] = @ItemId ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@VoteId", SqlDbType.Int);
            commandParameters[0].Value = voteId;
            commandParameters[1] = new SqlParameter("@ItemId", SqlDbType.Int);
            commandParameters[1].Value = itemId;
            commandParameters[2] = new SqlParameter("@IsAnonymity", SqlDbType.Bit);
            commandParameters[2].Value = isAnonymity;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar);
            commandParameters[3].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

